"""
教师管理视图函数
@file admin/teacher_views.py
@description 处理教师课程和班级学生成绩相关的视图函数
@created 2024-06-27
"""
from django.http import JsonResponse
from django.db import connection
import json
import logging
import random
from datetime import datetime, timedelta

# 设置日志
logger = logging.getLogger(__name__)

def teacher_courses(request, teacher_id):
    """
    获取教师授课的课程和班级信息
    @param request: HTTP请求对象
    @param teacher_id: 教师ID
    @return: 教师课程和班级信息
    """
    try:
        # 检查是否已经有课程数据，如果没有则生成测试数据
        ensure_course_data_exists()
        
        # 检查该教师是否已有课程分配，如果没有则为其分配课程
        ensure_teacher_has_courses(teacher_id)
        
        # 查询教师授课课程
        with connection.cursor() as cursor:
            # 查询教师课程信息
            cursor.execute("""
                SELECT c.course_id, c.name, tr.class_id, sc.name as class_name, 
                       COUNT(DISTINCT sc_st.student_id) as student_count
                FROM courses c
                JOIN teaching_record tr ON c.course_id = tr.course_id
                JOIN school_classes sc ON tr.class_id = sc.id
                LEFT JOIN student_class sc_st ON sc.id = sc_st.class_id
                WHERE tr.teacher_id = %s AND tr.status = 1
                GROUP BY c.course_id, c.name, tr.class_id, sc.name
            """, [teacher_id])
            
            course_results = cursor.fetchall()
            
            # 将结果转换为结构化数据
            courses = []
            for row in course_results:
                course_id, course_name, class_id, class_name, student_count = row
                
                # 查找已存在的课程
                course = next((c for c in courses if c['course_id'] == course_id), None)
                
                # 如果课程不存在，创建新课程
                if not course:
                    # 获取课程安排
                    cursor.execute("""
                        SELECT day_of_week, start_time, end_time
                        FROM course_schedules
                        WHERE course_id = %s AND teacher_id = %s
                    """, [course_id, teacher_id])
                    
                    schedule_results = cursor.fetchall()
                    schedule = ""
                    
                    if schedule_results:
                        day_map = ["周一", "周二", "周三", "周四", "周五", "周六", "周日"]
                        day, start, end = schedule_results[0]
                        if 0 <= day < len(day_map):
                            schedule = f"{day_map[day]} {start}-{end}节"
                    
                    course = {
                        'course_id': course_id,
                        'name': course_name,
                        'schedule': schedule,
                        'classes': []
                    }
                    courses.append(course)
                
                # 获取班级平均分和及格率
                cursor.execute("""
                    SELECT AVG(final_grade) as average_score,
                           SUM(CASE WHEN final_grade >= 60 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) as pass_rate
                    FROM grades g
                    JOIN student_class sc ON g.student_id = sc.student_id
                    WHERE sc.class_id = %s AND g.course_id = %s
                """, [class_id, course_id])
                
                score_result = cursor.fetchone()
                average_score = round(score_result[0], 1) if score_result[0] else 0
                pass_rate = round(score_result[1], 1) if score_result[1] else 0
                
                # 获取出勤率
                cursor.execute("""
                    SELECT COUNT(CASE WHEN ar.status = 1 THEN 1 END) * 100.0 / COUNT(*) as attendance_rate
                    FROM attendance_records ar
                    JOIN student_class sc ON ar.student_id = sc.student_id
                    WHERE sc.class_id = %s AND ar.course_id = %s
                """, [class_id, course_id])
                
                attendance_result = cursor.fetchone()
                attendance_rate = round(attendance_result[0], 1) if attendance_result and attendance_result[0] else 0
                
                # 添加班级信息
                course['classes'].append({
                    'class_id': class_id,
                    'name': class_name,
                    'student_count': student_count,
                    'average_score': average_score,
                    'pass_rate': pass_rate,
                    'attendance_rate': attendance_rate
                })
            
            return JsonResponse({
                'status': 'success',
                'message': '获取教师课程成功',
                'data': {
                    'courses': courses
                }
            })
            
    except Exception as e:
        logger.error(f"获取教师课程失败: {str(e)}")
        return JsonResponse({
            'status': 'error',
            'message': f'获取教师课程失败: {str(e)}',
            'data': None
        }, status=500)

def class_students_scores(request, teacher_id, course_id, class_id):
    """
    获取班级学生成绩信息
    @param request: HTTP请求对象
    @param teacher_id: 教师ID
    @param course_id: 课程ID
    @param class_id: 班级ID
    @return: 班级学生成绩信息
    """
    try:
        # 检查是否已经有课程数据，如果没有则生成测试数据
        ensure_course_data_exists()
        
        with connection.cursor() as cursor:
            # 验证教师是否教授此班级课程
            cursor.execute("""
                SELECT COUNT(*)
                FROM teaching_record
                WHERE teacher_id = %s AND course_id = %s AND class_id = %s AND status = 1
            """, [teacher_id, course_id, class_id])
            
            count = cursor.fetchone()[0]
            if count == 0:
                return JsonResponse({
                    'status': 'error',
                    'message': '该教师未授课此班级此门课程',
                    'data': None
                }, status=403)
            
            # 获取班级基本信息
            cursor.execute("""
                SELECT sc.name as class_name, c.name as course_name, t.teacher_name,
                       COUNT(DISTINCT sc_st.student_id) as student_count
                FROM school_classes sc
                JOIN courses c ON c.course_id = %s
                JOIN teacher t ON t.teacher_id = %s
                LEFT JOIN student_class sc_st ON sc.id = sc_st.class_id
                WHERE sc.id = %s
                GROUP BY sc.name, c.name, t.teacher_name
            """, [course_id, teacher_id, class_id])
            
            class_info = cursor.fetchone()
            if not class_info:
                return JsonResponse({
                    'status': 'error',
                    'message': '未找到班级信息',
                    'data': None
                }, status=404)
            
            class_name, course_name, teacher_name, student_count = class_info
            
            # 获取成绩统计信息
            cursor.execute("""
                SELECT AVG(g.final_grade) as average_score,
                       SUM(CASE WHEN g.final_grade >= 60 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) as pass_rate,
                       MAX(g.final_grade) as max_score
                FROM grades g
                JOIN student_class sc ON g.student_id = sc.student_id
                WHERE sc.class_id = %s AND g.course_id = %s
            """, [class_id, course_id])
            
            stats = cursor.fetchone()
            average_score = round(stats[0], 1) if stats[0] else 0
            pass_rate = round(stats[1], 1) if stats[1] else 0
            max_score = round(stats[2], 1) if stats[2] else 0
            
            # 获取学生成绩信息
            cursor.execute("""
                SELECT s.student_id, s.name,
                       g.final_grade as score,
                       (SELECT COUNT(CASE WHEN ar.status = 1 THEN 1 END) * 100.0 / NULLIF(COUNT(*), 0)
                        FROM attendance_records ar
                        WHERE ar.student_id = s.student_id AND ar.course_id = %s) as attendance_rate,
                       (SELECT COUNT(CASE WHEN hs.status = 'completed' THEN 1 END) * 100.0 / NULLIF(COUNT(*), 0)
                        FROM homework_submissions hs
                        WHERE hs.student_id = s.student_id AND hs.course_id = %s) as homework_rate
                FROM students s
                JOIN student_class sc ON s.student_id = sc.student_id
                LEFT JOIN grades g ON s.student_id = g.student_id AND g.course_id = %s
                WHERE sc.class_id = %s
                ORDER BY g.final_grade DESC
            """, [course_id, course_id, course_id, class_id])
            
            students_data = []
            for row in cursor.fetchall():
                student_id, name, score, attendance_rate, homework_rate = row
                
                students_data.append({
                    'student_id': student_id,
                    'name': name,
                    'score': round(score, 1) if score else 0,
                    'attendance_rate': round(attendance_rate, 1) if attendance_rate else 0,
                    'homework_rate': round(homework_rate, 1) if homework_rate else 0
                })
            
            return JsonResponse({
                'status': 'success',
                'message': '获取班级学生成绩成功',
                'data': {
                    'classInfo': {
                        'class_id': class_id,
                        'class_name': class_name,
                        'course_id': course_id,
                        'course_name': course_name,
                        'teacher_id': teacher_id,
                        'teacher_name': teacher_name,
                        'student_count': student_count
                    },
                    'stats': {
                        'average_score': average_score,
                        'pass_rate': pass_rate,
                        'max_score': max_score
                    },
                    'students': students_data
                }
            })
            
    except Exception as e:
        logger.error(f"获取班级学生成绩失败: {str(e)}")
        return JsonResponse({
            'status': 'error',
            'message': f'获取班级学生成绩失败: {str(e)}',
            'data': None
        }, status=500)


# ----- 生成测试数据相关函数 -----

def ensure_course_data_exists():
    """
    确保课程相关的数据存在，如果不存在则生成测试数据
    """
    with connection.cursor() as cursor:
        # 首先确保学院表中有数据
        cursor.execute("SELECT COUNT(*) FROM colleges")
        college_count = cursor.fetchone()[0]
        
        if college_count == 0:
            logger.info("学院表为空，添加基础学院数据...")
            colleges = [
                (1, "计算机科学与技术学院"),
                (2, "法学院"),
                (3, "经济与金融学院"),
                (4, "建筑工程学院"),
                (5, "管理学院")
            ]
            
            for college_id, name in colleges:
                try:
                    cursor.execute(
                        "INSERT INTO colleges (id, name) VALUES (%s, %s)",
                        [college_id, name]
                    )
                    logger.info(f"添加学院: {name}")
                except Exception as e:
                    logger.error(f"添加学院 {name} 失败: {str(e)}")
        
        # 检查courses表中是否有数据
        cursor.execute("SELECT COUNT(*) FROM courses")
        course_count = cursor.fetchone()[0]
        
        if course_count == 0:
            logger.info("未发现课程数据，开始生成测试数据...")
            generate_course_data()
        else:
            logger.info(f"已存在 {course_count} 条课程数据")


def ensure_teacher_has_courses(teacher_id):
    """
    确保指定教师有课程数据，如果没有则为其分配课程
    @param teacher_id: 教师ID
    """
    with connection.cursor() as cursor:
        # 检查该教师是否有教学记录
        cursor.execute("SELECT COUNT(*) FROM teaching_record WHERE teacher_id = %s", [teacher_id])
        record_count = cursor.fetchone()[0]
        
        if record_count == 0:
            logger.info(f"教师 {teacher_id} 没有课程数据，为其分配课程...")
            assign_courses_to_teacher(teacher_id)
        else:
            logger.info(f"教师 {teacher_id} 已有 {record_count} 条教学记录")


def generate_course_data():
    """
    生成基础测试数据，包括班级和学生
    """
    try:
        with connection.cursor() as cursor:
            # 确保学院表中有数据
            cursor.execute("SELECT COUNT(*) FROM colleges")
            college_count = cursor.fetchone()[0]
            
            if college_count == 0:
                logger.error("学院表为空，无法创建班级")
                return
                
            # 获取所有学院
            cursor.execute("SELECT id, name FROM colleges")
            colleges = cursor.fetchall()
            
            # 生成班级数据
            class_ids = []
            for college_id, college_name in colleges:
                # 每个学院创建多个班级
                # 不同年级
                for grade_year in [2020, 2021, 2022, 2023]:
                    # 不同专业
                    specialties = []
                    if college_id == 1:  # 计算机学院
                        specialties = ["计算机科学与技术", "软件工程", "人工智能", "网络工程"]
                    elif college_id == 2:  # 法学院
                        specialties = ["法学", "国际法", "经济法", "民商法"]
                    elif college_id == 3:  # 经济学院
                        specialties = ["经济学", "金融学", "国际经济与贸易", "财政学"]
                    elif college_id == 4:  # 工程学院
                        specialties = ["土木工程", "建筑学", "机械工程", "电气工程"]
                    elif college_id == 5:  # 管理学院
                        specialties = ["工商管理", "市场营销", "会计学", "人力资源管理"]
                    else:
                        specialties = ["通用专业"]
                        
                    for specialty in specialties:
                        # 每个专业1-2个班
                        for class_num in range(1, random.randint(2, 3)):
                            grade = f"{grade_year}级"
                            class_name = f"{college_name}{grade}{specialty}{class_num}班"
                            department = f"{specialty}系"
                            
                            try:
                                cursor.execute("""
                                    INSERT INTO school_classes (name, grade_id, college_id, department, status)
                                    VALUES (%s, %s, %s, %s, 1)
                                """, [
                                    class_name,
                                    grade_year,  # 使用年份作为grade_id
                                    college_id,
                                    department
                                ])
                                
                                # 获取最后插入的ID
                                cursor.execute("SELECT LAST_INSERT_ID()")
                                class_id = cursor.fetchone()[0]
                                class_ids.append(class_id)
                                logger.info(f"创建班级: {class_name} (ID: {class_id})")
                                
                                # 为每个班级生成20-40名学生
                                student_count = random.randint(20, 40)
                                for j in range(1, student_count + 1):
                                    student_number = f"{grade_year}{college_id:02d}{class_id:03d}{j:03d}"
                                    student_name = f"学生{student_number}"
                                    
                                    # 检查学生是否已存在
                                    cursor.execute("SELECT student_id FROM students WHERE name = %s", [student_name])
                                    student = cursor.fetchone()
                                    
                                    if student:
                                        student_id = student[0]
                                    else:
                                        cursor.execute("""
                                            INSERT INTO students (name, email, enrollment_date)
                                            VALUES (%s, %s, %s)
                                        """, [
                                            student_name,
                                            f"student_{student_number}@example.com",
                                            datetime.now() - timedelta(days=random.randint(365, 730))
                                        ])
                                        
                                        # 获取最后插入的ID
                                        cursor.execute("SELECT LAST_INSERT_ID()")
                                        student_id = cursor.fetchone()[0]
                                    
                                    # 将学生与班级关联
                                    try:
                                        cursor.execute("""
                                            INSERT INTO student_class (student_id, class_id)
                                            VALUES (%s, %s)
                                        """, [student_id, class_id])
                                    except Exception as e:
                                        # 可能已存在记录，忽略错误
                                        pass
                            except Exception as e:
                                logger.error(f"创建班级失败: {str(e)}")
            
            # 获取所有教师ID
            cursor.execute("SELECT teacher_id FROM teacher")
            all_teacher_ids = [row[0] for row in cursor.fetchall()]
            
            if not all_teacher_ids:
                logger.warning("未找到教师数据，将不会分配课程")
            else:
                # 为每个教师分配课程
                for teacher_id in all_teacher_ids:
                    assign_courses_to_teacher(teacher_id)
            
            logger.info("班级和学生数据生成完成")
            
    except Exception as e:
        logger.error(f"生成测试数据失败: {str(e)}")


def assign_courses_to_teacher(teacher_id):
    """
    为指定教师分配课程，确保只分配对应学院的课程和班级
    @param teacher_id: 教师ID
    """
    try:
        with connection.cursor() as cursor:
            # 获取教师所属学院
            cursor.execute("SELECT college_id FROM teacher WHERE teacher_id = %s", [teacher_id])
            result = cursor.fetchone()
            
            if not result or not result[0]:
                logger.error(f"未找到教师 {teacher_id} 或教师没有关联的学院")
                
                # 如果教师没有学院，随机分配一个
                college_id = random.randint(1, 5)
                logger.info(f"为教师 {teacher_id} 随机分配学院ID: {college_id}")
                
                # 更新教师的学院ID
                cursor.execute("UPDATE teacher SET college_id = %s WHERE teacher_id = %s", [college_id, teacher_id])
            else:
                college_id = result[0]
            
            # 根据学院选择合适的课程
            course_names_by_college = {
                1: ["数据结构", "计算机组成原理", "操作系统", "计算机网络", "数据库原理", "软件工程", "算法设计与分析", "人工智能"], # 计算机学院
                2: ["法学导论", "宪法学", "民法学", "刑法学"], # 法学院
                3: ["经济学原理", "金融学", "国际经济学", "财务管理", "统计学", "会计学原理"], # 经济学院
                4: ["高等数学", "线性代数", "概率论与数理统计", "物理学", "工程力学"], # 工程学院
                5: ["管理学原理", "市场营销", "人力资源管理", "组织行为学"] # 管理学院
            }
            
            # 获取学院对应的课程名称
            college_course_names = course_names_by_college.get(college_id, [])
            
            # 如果没有对应的课程，使用通用课程
            if not college_course_names:
                college_course_names = ["通识课程", "综合素质培养", "职业发展规划"]
            
            # 随机选择1-3门课程
            num_courses = random.randint(1, 3)
            selected_courses = random.sample(college_course_names, min(num_courses, len(college_course_names)))
            
            # 获取学院名称
            cursor.execute("SELECT name FROM colleges WHERE id = %s", [college_id])
            college_result = cursor.fetchone()
            college_name = college_result[0] if college_result else f"学院{college_id}"
            
            logger.info(f"为学院 '{college_name}' 的教师 {teacher_id} 分配课程")
            
            for course_name in selected_courses:
                # 获取教师姓名
                cursor.execute("SELECT teacher_name FROM teacher WHERE teacher_id = %s", [teacher_id])
                teacher_name_result = cursor.fetchone()
                teacher_name = teacher_name_result[0] if teacher_name_result else f"教师{teacher_id}"
                
                # 创建课程
                cursor.execute("""
                    INSERT INTO courses (name, description, status, teacher_id, teacher_name)
                    VALUES (%s, %s, 1, %s, %s)
                """, [
                    course_name,
                    f"{course_name}是一门重要的专业课程。",
                    teacher_id,
                    teacher_name
                ])
                
                # 获取最后插入的ID
                cursor.execute("SELECT LAST_INSERT_ID()")
                course_id = cursor.fetchone()[0]
                logger.info(f"为教师 {teacher_id} 创建课程: {course_name} (ID: {course_id})")
                
                # 仅选择同学院的班级
                cursor.execute("""
                    SELECT id FROM school_classes 
                    WHERE college_id = %s 
                    LIMIT 20
                """, [college_id])
                
                college_class_ids = [row[0] for row in cursor.fetchall()]
                
                if not college_class_ids:
                    logger.warning(f"学院 {college_id} 没有找到班级，无法分配课程")
                    continue
                
                num_classes = min(random.randint(1, 2), len(college_class_ids))
                selected_class_ids = random.sample(college_class_ids, num_classes)
                
                for class_id in selected_class_ids:
                    # 创建教学记录
                    cursor.execute("""
                        INSERT INTO teaching_record (teacher_id, course_id, class_id, semester_id, start_date, end_date, status)
                        VALUES (%s, %s, %s, %s, %s, %s, 1)
                    """, [
                        teacher_id,
                        course_id,
                        class_id,
                        1,  # 学期ID
                        datetime.now() - timedelta(days=90),  # 开课日期（3个月前）
                        datetime.now() + timedelta(days=90),  # 结课日期（3个月后）
                    ])
                    logger.info(f"创建教学记录: 教师 {teacher_id}, 课程 {course_id}, 班级 {class_id}")
                    
                    # 创建课程安排
                    day_of_week = random.randint(0, 4)  # 周一至周五
                    start_time = random.randint(1, 7)  # 第1-7节
                    end_time = start_time + 1
                    
                    cursor.execute("""
                        INSERT INTO course_schedules (course_id, teacher_id, day_of_week, start_time, end_time, classroom)
                        VALUES (%s, %s, %s, %s, %s, %s)
                    """, [
                        course_id,
                        teacher_id,
                        day_of_week,
                        start_time,
                        end_time,
                        f"教学楼{random.randint(1, 5)}-{random.randint(101, 501)}"
                    ])
                    logger.info(f"创建课程安排: 教师 {teacher_id}, 课程 {course_id}, 时间: 周{day_of_week+1} {start_time}-{end_time}节")
                    
                    # 为班级学生生成成绩和出勤记录
                    cursor.execute("SELECT student_id FROM student_class WHERE class_id = %s", [class_id])
                    student_ids = [row[0] for row in cursor.fetchall()]
                    
                    for student_id in student_ids:
                        # 成绩
                        final_grade = round(random.uniform(60, 100), 1)
                        cursor.execute("""
                            INSERT INTO grades (student_id, course_id, final_grade)
                            VALUES (%s, %s, %s)
                        """, [student_id, course_id, final_grade])
                        
                        # 出勤记录（假设有10次课）
                        for i in range(10):
                            status = 1 if random.random() > 0.1 else 0  # 90%出勤率
                            try:
                                cursor.execute("""
                                    INSERT INTO attendance_records (student_id, course_id, class_date, status)
                                    VALUES (%s, %s, %s, %s)
                                """, [
                                    student_id,
                                    course_id,
                                    datetime.now() - timedelta(days=90-i*7),  # 每周一次课
                                    status
                                ])
                            except Exception as e:
                                # 可能已存在记录，忽略错误
                                pass
            
            logger.info(f"成功为教师 {teacher_id} 分配了 {len(selected_courses)} 门课程")
            
    except Exception as e:
        logger.error(f"为教师 {teacher_id} 分配课程失败: {str(e)}") 